﻿using System;
using System.Collections.Generic;
using System.Text;
using HotelManagement.Common;
using HotelManagement.DL.Helper;
using System.Data.Common;
using System.Data;
using HotelManagement.Common.SQL;

namespace HotelManagement.DL.DAO
{
    public class TradeDL
    {
        #region Constant
        private readonly string SQL_PARA_TYPE = "Type";
        private readonly string SQL_PARA_GOODS_ID = "Goods_ID";
        private readonly string SQL_PARA_COUNT = "Count";
        private readonly string SQL_PARA_COST = "Cost";
        private readonly string SQL_PARA_DATE = "Date";
        private readonly string SQL_PARA_GOODS_NAME = "@Goods_Name";
        private readonly string SQL_GOODS_NAME = " Goods_Name = @Goods_Name ";
        private readonly string SQL_PARAM_TYPE = "@Type";
        private readonly string SQL_TYPE = " [Type] = @Type ";
        private readonly string SQL_PARAM_DATE_START = "@DateStart";
        private readonly string SQL_DATE_START = " [Date] >= @DateStart ";
        private readonly string SQL_PARAM_DATE_END = "@DateEnd";
        private readonly string SQL_DATE_END = " [Date] <= @DateEnd ";
        private readonly string SQL_AND = " AND ";
        private readonly string SQL_WHERE = " WHERE ";
        #endregion

        #region Public Method
        public int AddTrade(Trade tradeInfo, DbTransaction trans)
        {
            int nResult = 0;
            try
            {
                DBHelper dbHelper = DBHelperFactory.createDBHelper();
                DbCommand command = dbHelper.CreateCommandBySql(SqlFile.SQL_ADD_TRADE);
                dbHelper.AddInParameter(command, "Type", DbType.String, tradeInfo.Type);
                dbHelper.AddInParameter(command, "Goods_ID", DbType.String, tradeInfo.GoodsID);
                dbHelper.AddInParameter(command, "Goods_Name", DbType.String, tradeInfo.GoodsName);
                dbHelper.AddInParameter(command, "Count", DbType.Int32, tradeInfo.Count);
                dbHelper.AddInParameter(command, "Cost", DbType.Single, tradeInfo.Cost);
                dbHelper.AddInParameter(command, "Date", DbType.DateTime, tradeInfo.TradeTime.ToString("yyyy/MM/dd hh:mm:ss"));

                nResult = dbHelper.ExecuteNonQuery(command, trans);
            }
            catch (Exception)
            {
                throw;
            }

            return nResult;
        }

        public int SearchTradeInfo(ref Trade trade) 
        {
            int nResult = (int)ReturnType.SUCCESS;
            try
            {
                DBHelper dbHelper = DBHelperFactory.createDBHelper();
                DbCommand command = null;
                string strSql = SqlFile.SQL_SEARCH_TRADE;
                int n = 0;
                Dictionary<string, string> dic = new Dictionary<string, string>();
                if (!string.IsNullOrEmpty(trade.GoodsName))
                {
                    strSql = AddAndToSql(strSql, SQL_GOODS_NAME, n);
                    dic.Add(SQL_PARA_GOODS_NAME, trade.GoodsName);
                    n++;
                }
                if (!string.IsNullOrEmpty(trade.Type))
                {
                    strSql = AddAndToSql(strSql, SQL_TYPE, n);
                    dic.Add(SQL_PARAM_TYPE, trade.Type);
                    n++;
                }
                if (!string.IsNullOrEmpty(trade.TradeTimeStart))
                {
                    strSql = AddAndToSql(strSql, SQL_DATE_START, n);
                    dic.Add(SQL_PARAM_DATE_START, trade.TradeTimeStart);
                    n++;
                }
                if (!string.IsNullOrEmpty(trade.TradeTimeEnd))
                {
                    strSql = AddAndToSql(strSql, SQL_DATE_END, n);
                    dic.Add(SQL_PARAM_DATE_END, trade.TradeTimeEnd);
                    n++;
                }
                command = dbHelper.CreateCommandBySql(strSql);

                foreach (string key in dic.Keys) 
                {
                    if (key.Equals(SQL_PARAM_DATE_START) || key.Equals(SQL_PARAM_DATE_END))
                    {
                        dbHelper.AddInParameter(command, key, DbType.DateTime, dic[key]);
                    }
                    else 
                    {
                        dbHelper.AddInParameter(command, key, DbType.String, dic[key]);
                    } 
                }

                DataSet ds = dbHelper.ExecuteDataSetQuery(command);
                if (0 == ds.Tables.Count)
                {
                    nResult = (int)ReturnType.FAILED;
                }
                trade.TradeCollection = ds.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }

            return nResult;
        }

        #endregion

        #region Private Method

        private string AddAndToSql(string sql, string condition, int n) 
        {
            if (n > 0)
            {
                return sql + SQL_AND + condition;
            }
            else 
            {
                return sql + SQL_WHERE +condition;
            }
        }

        #endregion
    }
}
